﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using QL_DTTN.DAL;
namespace QL_DTTN.DTO.DTO
{
   public partial class LichThi
    {
       public DataTable dataTable(Int64 maKyThi)
       {
           string sql = "select ThiSinh.soBaoDanh, hoTen, tenKyThi, tenPhongThi, tenDiemThi, DiemThi.diaChi, tenMon from DiemThi, Mon, MonThi, PhongThi, LichThi, KyThi, ThiSinh where Mon.maMon = MonThi.maMon and MonThi.maMonThi = LichThi.maMonThi and PhongThi.maPhongThi = LichThi.maPhongThi and PhongThi.maDiemThi=DiemThi.maDiemThi and ThiSinh.soBaoDanh=LichThi.soBaoDanh and ThiSinh.maKyThi=KyThi.maKyThi and KyThi.maKyThi=@pMaKyThi";
           SqlDataProvider dp = new SqlDataProvider();
           dp.AddParameter(new SqlParameter("@pMaKyThi", maKyThi));
           return dp.ExDataTable(sql, true);
       }
       public DataTable DataLichThi(string soBaoDanh)
       {
           string sql = " select M2.TenMon,D.TenDiemThi,D.DiaChi,P.TenPhongThi,M.ThoiGianBatDau,M.ThoiGianKetThuc from " + _tableName + " C ";
           sql += " left join PhongThi P on P.MaPhongThi=C.MaPhongThi ";
           sql += " left join DiemThi D on P.MaDiemThi=D.MaDiemThi ";
           sql += " left join MonThi M on M.MaMonThi=C.MaMonThi ";
           sql += " left join Mon M2 on M.MaMon=M2.MaMon ";
           sql+=" where C.SoBaoDanh=@pSoBaoDanh  ";
           SqlDataProvider dp = new SqlDataProvider();
           dp.AddParameter(new SqlParameter("@pSoBaoDanh", soBaoDanh ));
           return dp.ExDataTable(sql, true);
       }
       public DataTable getLichThi(string key, short maTinh, string maTruong, short maHeDaoTao, long maKyThi, long maMonThi)
       {
           string sql = "select T.SoBaoDanh,T.HoTen,M2.TenMon,P.TenPhongThi,D.TenDiemThi,D.DiaChi,K.TenKyThi ";
           sql += " from ThiSinh T inner join Truong on Truong.MaTruong=T.MaTruong ";
           sql += " left join ChiTietMonThi C on T.MaHeDaoTao=C.MaHeDaoTao ";
           sql += " left join LichThi L on T.SoBaoDanh=L.SoBaoDanh and C.MaMonThi=L.MaMonThi ";
           sql += " left join MonThi M on M.MaMonThi=C.MaMonThi ";
           sql += " left join Mon M2 on M.MaMon=M2.MaMon ";
           sql += " left join PhongThi P on P.MaPhongThi=L.MaPhongThi ";
           sql += " inner join DiemThi D on D.MaDiemThi=P.MaDiemThi ";
           sql += " left join KyThi K on T.MaKyThi=K.MaKyThi ";
           sql += " where (HoTen Like @pKey or T.SoBaoDanh Like @pKey) ";
           sql += ((maTruong == "0") ? ((maTinh == 0) ? "" : " and Truong.MaTinh=@pMaTinh ") : " and T.MaTruong = @pMaTruong ");
           sql += ((maHeDaoTao == 0) ? "" : " and T.MaHeDaoTao = @pMaHeDaoTao ");
           sql += ((maKyThi == 0) ? "" : " and T.MaKyThi = @pMaKyThi ");
           sql += ((maMonThi == 0) ? "" : " and C.MaMonThi = @pMaMonThi ");
           SqlDataProvider dp = new SqlDataProvider();
           dp.AddParameter(new SqlParameter("@pKey", "%" + key + "%"));
           dp.AddParameter(new SqlParameter("@pMaTruong", maTruong));
           dp.AddParameter(new SqlParameter("@pMaTinh", maTinh));
           dp.AddParameter(new SqlParameter("@pMaHeDaoTao", maHeDaoTao));
           dp.AddParameter(new SqlParameter("@pMaKyThi", maKyThi));
           dp.AddParameter(new SqlParameter("@pMaMonThi", maMonThi));
           return dp.ExDataTable(sql, true);
       }
    }
}
